#include "localdb.h"

LocalDB::LocalDB(QObject *parent) : QObject(parent)
{

}

void LocalDB::CreateDB(QString userId){
    if(QSqlDatabase::contains(userId))
    {
        db = QSqlDatabase::database(userId);
        qDebug() << "existed!";
    }
    else
    {
        db = QSqlDatabase::addDatabase("QSQLITE", userId);
        db.setDatabaseName(userId + ".db");
//        db.setUserName(userId);
//        db.setPassword(password);
        qDebug() << "created!";
    }
}

bool LocalDB::OpenDB(){
    if(!db.open())
    {
        qDebug() << "Error: Failed to connect database." << db.lastError();
        return false;
    }
    qDebug() << "Open!";
    return true;
}

void LocalDB::CloseDB(){
    db.close();
}

void LocalDB::ExecSql(QString sql){
    QSqlQuery sql_query(db);

    if(!sql_query.exec(sql))
    {
        qDebug() << "Error: Failed!." << sql_query.lastError();
    }
    else
    {
        qDebug() << "Well done!" << sql;
    }
}

void LocalDB::Init(QString userId){
    // 创建该用户的数据库以及基本表
    db = QSqlDatabase::addDatabase("QSQLITE", userId);
    db.setDatabaseName(userId + ".db");
    qDebug() << "created!";
    this->OpenDB();
//    CreateTableFriendCategory();
    CreateTableFriend();
//    CreateTableGroupCategory();
    CreateTableGroup();
//    CreateTableGroupFriend();
    CreateTableMessage();
}

void LocalDB::Connect(QString userId){
    if(QSqlDatabase::contains(userId))
    {
        db = QSqlDatabase::database(userId);
        qDebug() << "connected!";
        this->OpenDB();
    }
    else
    {
        this->Init(userId);
    }
}

void LocalDB::Drop(QString dbName){
    QFile::remove(dbName + ".db");
    qDebug() << "removed!";
}

//void LocalDB::CreateTableFriendCategory(){
//    QString sql = "CREATE TABLE friendcategory("
//                  "id char(20) PRIMARY KEY, "
//                  "name varchar(20))";
//    this->ExecSql(sql);
//}

void LocalDB::CreateTableFriend(){
    QString sql = "CREATE TABLE friend("
                  "id int PRIMARY KEY, "
                  "name varchar(20), "
                  "ip varchar(20), "
                  "job varchar(20)) ";
//                  "FOREIGN KEY (job) REFERENCES friendcategory(id))";

    this->ExecSql(sql);
}

//void LocalDB::CreateTableGroupCategory(){
//    QString sql = "CREATE TABLE friendgroup("
//                  "id char(20) PRIMARY KEY, "
//                  "name varchar(20))";
//    this->ExecSql(sql);
//}

void LocalDB::CreateTableGroup(){
    QString sql = "CREATE TABLE mygroup("
                  "id int PRIMARY KEY, "
                  "name varchar(20), "
                  "memberList varchar(200), "
                  "category char(20)) ";
//                  "FOREIGN KEY (categoryId) REFERENCES groupcategory(id))";

    this->ExecSql(sql);
}

//void LocalDB::CreateTableGroupFriend(){
//    QString sql = "CREATE TABLE groupfriend("
//                  "groupid char(20), "
//                  "friendid char(20), "
//                  "PRIMARY KEY (groupid, friendid), "
//                  "FOREIGN KEY (groupid) REFERENCES mygroup(id)) ";
//    this->ExecSql(sql);
//}

void LocalDB::CreateTableMessage(){
    QString sql = "CREATE TABLE message("
                  "id int PRIMARY KEY, "
                  "tid int, "
                  "rid int, "
                  "msg varchar(200), "
                  "time TEXT)"
                  ;
    this->ExecSql(sql);
}

void LocalDB::AddFriend(int id, QString name, QString ip, QString job){
    this->OpenDB();
    QString sql = "INSERT INTO friend values(?, ?, ?, ?)";
    QSqlQuery query(db);
    query.prepare(sql);
    query.addBindValue(id);
    query.addBindValue(name);
    query.addBindValue(ip);
    query.addBindValue(job);
    if(query.exec()){
        qDebug() << "friend数据插入成功！";
    }
    else{
        qDebug() << "friend插入失败" << query.lastError();
    }
}

void LocalDB::AddFriend(Friend frd){
    this->OpenDB();
    QString sql = "INSERT INTO friend values(?, ?, ?, ?)";
    QSqlQuery query(db);
    query.prepare(sql);
    query.addBindValue(frd.id);
    query.addBindValue(frd.name);
    query.addBindValue(frd.ip);
    query.addBindValue(frd.job);
    if(query.exec()){
        qDebug() << "friend数据插入成功！";
    }
    else{
        qDebug() << "friend插入失败";
    }
}

Friend LocalDB::GetFriend(int id){
    this->OpenDB();
    Friend frd;
    QSqlQuery query(db);
    QString sql = "SELECT * FROM friend WHERE id="+QString::number(id);
    if (query.exec(sql)){
        while(query.next()){
            frd.id = query.value(0).toInt();
            frd.name = query.value(1).toString();
            frd.ip = query.value(2).toString();
            frd.job = query.value(3).toString();
        }
    }
    else{
        qDebug() << "查询friend " << id << " 失败";
    }
    return frd;
}

QVector<Friend> LocalDB::GetAllFriends(){
    this->OpenDB();
    QVector<Friend> frds;
    Friend frd;
    QSqlQuery query(db);
    QString sql = "SELECT * FROM friend";
    if (query.exec(sql)){
        while(query.next()){
            frd.id = query.value(0).toInt();
            frd.name = query.value(1).toString();
            frd.ip = query.value(2).toString();
            frd.job = query.value(3).toString();
            frds.append(frd);
        }
    }
    else{
        qDebug() << "获取friend数据失败";
    }
    return frds;
}

void LocalDB::AddGroup(int id, QString name, QString memberList, QString category){
    this->OpenDB();
    QString sql = "INSERT INTO mygroup values(?, ?, ?, ?)";
    QSqlQuery query(db);
    query.prepare(sql);
    query.addBindValue(id);
    query.addBindValue(name);
    query.addBindValue(memberList);
    query.addBindValue(category);
    if(query.exec()){
        qDebug() << "group数据插入成功！";
    }
    else{
        qDebug() << "group插入失败" << query.lastError();
    }
}

void LocalDB::AddGroup(Group gp){
    this->OpenDB();
    QString sql = "INSERT INTO mygroup values(?, ?, ?, ?)";
    QSqlQuery query(db);
    query.prepare(sql);
    query.addBindValue(gp.id);
    query.addBindValue(gp.name);
    query.addBindValue(gp.memberlist);
    query.addBindValue(gp.category);
    if(query.exec()){
        qDebug() << "group数据插入成功！";
    }
    else{
        qDebug() << "group插入失败";
    }
}

Group LocalDB::GetGroup(int id){
    this->OpenDB();
    Group gp;
    QSqlQuery query(db);
    QString sql = "SELECT * FROM mygroup WHERE id="+QString::number(id);
    if (query.exec(sql)){
        while(query.next()){
            gp.id = query.value(0).toInt();
            gp.name = query.value(1).toString();
            gp.memberlist = query.value(2).toString();
            gp.category = query.value(3).toString();
        }
    }
    else{
        qDebug() << "查询group " << id << " 失败";
    }
    return gp;
}

QVector<Group> LocalDB::GetAllGroups(){
    this->OpenDB();
    QVector<Group> gps;
    Group gp;
    QSqlQuery query(db);
    QString sql = "SELECT * FROM mygroup";
    if (query.exec(sql)){
        while(query.next()){
            gp.id = query.value(0).toInt();
            gp.name = query.value(1).toString();
            gp.memberlist = query.value(2).toString();
            gp.category = query.value(3).toString();
            gps.append(gp);
        }
    }
    else{
        qDebug() << "获取group数据失败";
    }
    return gps;
}

void LocalDB::AddMessage(Message msg){
    this->OpenDB();
    QString sql = "INSERT INTO message values(?, ?, ?, ?, ?)";
    QSqlQuery query(db);
    query.prepare(sql);
    query.addBindValue(msg.id);
    query.addBindValue(msg.tid);
    query.addBindValue(msg.rid);
    query.addBindValue(msg.msg);
    query.addBindValue(msg.time.toString("yyyy-MM-dd hh:mm:ss"));
    if(query.exec()){
        qDebug() << "message数据插入成功！";
    }
    else{
        qDebug() << "message插入失败";
    }
}

void LocalDB::AddMessages(QVector<Message> msgs){
    this->OpenDB();
    QString sql = "INSERT INTO message values(?, ?, ?, ?, ?)";
    QSqlQuery query(db);
    for(auto msg=msgs.begin(); msg!=msgs.end(); msg++){
        query.prepare(sql);
        query.addBindValue(msg->id);
        query.addBindValue(msg->tid);
        query.addBindValue(msg->rid);
        query.addBindValue(msg->msg);
        query.addBindValue(msg->time.toString("yyyy-MM-dd hh:mm:ss"));
        if(query.exec()){
            qDebug() << "messages数据插入成功！";
        }
        else{
            qDebug() << "messages插入失败";
            break;
        }
    }


}

Message LocalDB::GetLastMessageByTid(int tid){
    this->OpenDB();
    Message msg;
    QSqlQuery query(db);
    QString sql = "SELECT * FROM message WHERE tid="+QString::number(tid) + " ORDER BY id DESC";
    if (query.exec(sql)){
        while(query.next()){
            msg.id = query.value(0).toInt();
            msg.tid = query.value(1).toInt();
            msg.rid = query.value(2).toInt();
            msg.msg = query.value(3).toString();
            msg.time = QDateTime::fromString(query.value(4).toString(),"yyyy-MM-dd hh:mm:ss");
            break;
        }
    }
    else{
        qDebug() << "查询 " << tid << " 最后一条信息失败";
    }
    return msg;
}

QVector<Message> LocalDB::GetSomeRecentMessageByTid(int tid, int num){
    this->OpenDB();
    Message msg;
    QVector<Message> msgs;
    QSqlQuery query(db);
    QString sql = "SELECT * FROM message WHERE tid="+QString::number(tid) + " ORDER BY id DESC";
    if (query.exec(sql)){
        while(query.next() && num--){
            msg.id = query.value(0).toInt();
            msg.tid = query.value(1).toInt();
            msg.rid = query.value(2).toInt();
            msg.msg = query.value(3).toString();
            msg.time = QDateTime::fromString(query.value(4).toString(),"yyyy-MM-dd hh:mm:ss");
            msgs.append(msg);
        }
    }
    else{
        qDebug() << "查询 " << tid << " 最近的几条信息失败";
    }
    return msgs;
}

Message LocalDB::GetLastMessageByRid(int tid){
    this->OpenDB();
    Message msg;
    QSqlQuery query(db);
    QString sql = "SELECT * FROM message WHERE rid="+QString::number(tid) + " ORDER BY id DESC";
    if (query.exec(sql)){
        while(query.next()){
            msg.id = query.value(0).toInt();
            msg.tid = query.value(1).toInt();
            msg.rid = query.value(2).toInt();
            msg.msg = query.value(3).toString();
            msg.time = QDateTime::fromString(query.value(4).toString(),"yyyy-MM-dd hh:mm:ss");
            break;
        }
    }
    else{
        qDebug() << "查询 " << tid << " 最后一条信息失败";
    }
    return msg;
}

QVector<Message> LocalDB::GetSomeRecentMessageByRid(int tid, int num){
    this->OpenDB();
    Message msg;
    QVector<Message> msgs;
    QSqlQuery query(db);
    QString sql = "SELECT * FROM message WHERE rid="+QString::number(tid) + " ORDER BY id DESC";
    if (query.exec(sql)){
        while(query.next() && num--){
            msg.id = query.value(0).toInt();
            msg.tid = query.value(1).toInt();
            msg.rid = query.value(2).toInt();
            msg.msg = query.value(3).toString();
            msg.time = QDateTime::fromString(query.value(4).toString(),"yyyy-MM-dd hh:mm:ss");
            msgs.append(msg);
        }
    }
    else{
        qDebug() << "查询 " << tid << " 最近的几条信息失败";
    }
    return msgs;
}

int LocalDB::GetMaxMessageId(){
    this->OpenDB();
    QSqlQuery query(db);
    QString sql = "SELECT MAX(id) FROM message";
    if (query.exec(sql)){
        while(query.next()){
            return query.value(0).toInt();
        }
    }
    else{
        qDebug() << "查询最后一条信息id失败";
    }
    return -1;
}

//void LocalDB::Select(QString sql){
//    QSqlQuery sql_query(db);

//    if(!sql_query.exec(sql))
//    {
//        qDebug() << "Error: Failed!." << sql_query.lastError();
//        return ;
//    }
//    while(sql_query.next())
//            {
//                int id = sql_query.value(0).toInt();
//                QString name = sql_query.value(1).toString();
//                int age = sql_query.value(2).toInt();
//                qDebug()<<QString("id:%1    name:%2    age:%3").arg(id).arg(name).arg(age);
//            }

//}
